package com.cpi.cornpp.purchase.program.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.purchase.program.dto.PurchaseProgramDTO;
import com.cpi.cornpp.purchase.program.exception.PurchaseProgramException;
import com.cs.common.init.SystemParameterCollection;
import com.cs.common.init.beans.SystemParameterBean;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Purchase Program Data Access
 * @author Edgar Joao
 */
public class PurchaseProgramDAO extends DBDAO {

	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** SQL List Purchase Program */ 
	private static final String SQL_LIST_PURCHASE_PROGRAM =  "SELECT period + ' ' + convert( varchar, year ), code, code_number, capacity AS futures, weight AS tons, price_bushel AS priceBu, isnull( base_ton, 0 ) * (select 1/qty from units where unit_id = 'BUS') base_bushel, price_bushel+isnull( (base_ton* (select 1/qty from units where unit_id = 'BUS')), 0 ) AS priceUsBu, isnull( (price_bushel/(select 1/qty from units where unit_id = 'BUS')), 0 ) +isnull( base_ton, 0 ) AS priceUsTm, futures_pondered_value_bushel real_value, isnull( futures_pondered_value_bushel, 0 )+isnull( (base_ton * (select 1/qty from units where unit_id = 'BUS')), 0 ) AS valBase, isnull( (futures_pondered_value_bushel/(select 1/qty from units where unit_id = 'BUS')), 0 )+isnull( base_ton, 0 ) AS  cRealTm, purchase_order_id, isnull( weight, -1 ), isnull(futures_pondered_value_bushel, -1), isnull( price_bushel, -1 )  FROM vw_purchases_orders_program where convert( datetime, convert( varchar, year ) +(case when month < 10 then '0' else '' end)+convert( varchar, month )+'0'+convert( varchar, biweek ) ) between (select convert( datetime, convert( varchar, year ) +(case when month < 10 then '0' else '' end)+convert( varchar, month )+'0'+convert( varchar, biweek ) ) from biweeks_periods where biweek_period_id = ?) and (select convert( datetime, convert( varchar, year ) +(case when month < 10 then '0' else '' end)+convert( varchar, month )+'0'+convert( varchar, biweek ) ) from biweeks_periods where biweek_period_id = ?) ";//$NON-NLS-1$
	/** SQL Delete Purchase Program By Id*/
	private static final String SP_DELETE_PURCHASE_ORDER = "exec sp_deletePurchaseOrder ?";//$NON-NLS-1$
	/** Transport filter*/
	private static final String TRANSPORT_FILTER = " and transport_id = ?";//$NON-NLS-1$
	/**
	 * 
	 * List Of Purchase Program
	 * 
	 * @author Edgar Joao
	 * @param orderByColumn
	 * @param ascendingOrder
	 * @return
	 * @throws Exception
	 */
	public List list(int orderByColumn, boolean ascendingOrder, int biWeekInit, int biWeekEnd, int transportId) throws Exception{
		StringBuffer sql = null;
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		PurchaseProgramDTO purchaseProgramDTO = null;
		
		sql = new StringBuffer(SQL_LIST_PURCHASE_PROGRAM);
		if( transportId > -1 ){
			sql.append(TRANSPORT_FILTER);
		}
		sql.append(SQL_ORDER_BY);
		
		switch(orderByColumn){
			case 0: sql.append(PurchaseProgramDTO.COMPLETE_PERIOD);
					if (ascendingOrder)	{
						sql.append(SQL_ASCENDING_ORDER);
					} else {
						sql.append(SQL_DESCENDING_ORDER);
					}
					sql.append(SQL_COMMA);
			case 1: sql.append(PurchaseProgramDTO.B_NO);
					break;
			case 2: sql.append(PurchaseProgramDTO.B_NO_NUMBER);
					break;				
			case 3: sql.append(PurchaseProgramDTO.FUTURES);
					break;
			case 4: sql.append(PurchaseProgramDTO.TONS);
					break;
			case 5: sql.append(PurchaseProgramDTO.PRICE_BU);
					break;
			case 6: sql.append(PurchaseProgramDTO.BASE_BU);
					break;
			case 7: sql.append(PurchaseProgramDTO.PRICE_US_BU);		
					break;
			case 8: sql.append(PurchaseProgramDTO.Price_US_TM);		
					break;	
			case 9: sql.append(PurchaseProgramDTO.VAL_REAL_FUT);		
					break;		
		   case 10: sql.append(PurchaseProgramDTO.VAL_BASE);		
					break;		
		   case 11: sql.append(PurchaseProgramDTO.C_REAL_TM);		
					break;							   			
		}		
		if (ascendingOrder)	{
			sql.append(SQL_ASCENDING_ORDER);
		} else {
			sql.append(SQL_DESCENDING_ORDER);
		}

		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(sql.toString(), DATASOURCE);
			preparedStatement.setInt(1, biWeekInit);
			preparedStatement.setInt(2, biWeekEnd);
			if( transportId > -1 ){
				preparedStatement.setInt(3, transportId);
			}
			
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				purchaseProgramDTO = new PurchaseProgramDTO();
				purchaseProgramDTO.setPeriod(rst.getString(1));
				purchaseProgramDTO.setbNo(rst.getString(2));
				purchaseProgramDTO.setbNoNumber(rst.getInt(3));
				purchaseProgramDTO.setFutures(rst.getDouble(4));
				purchaseProgramDTO.setTons(rst.getDouble(5));
				purchaseProgramDTO.setPriceBu(rst.getDouble(6));
				purchaseProgramDTO.setBaseBu(rst.getDouble(7));
				purchaseProgramDTO.setPriceUsBu(rst.getDouble(8));
				purchaseProgramDTO.setPriceUsTm(rst.getDouble(9));
				purchaseProgramDTO.setValRealFut(rst.getDouble(10));
				purchaseProgramDTO.setValBase(rst.getDouble(11));
				purchaseProgramDTO.setcRealTm(rst.getDouble(12));
				purchaseProgramDTO.setPurchaseOrderId(rst.getInt(13));
				purchaseProgramDTO.setSapAssigned(rst.getString(14));
				purchaseProgramDTO.setFutureAssigned(rst.getString(15));
				purchaseProgramDTO.setBushelPriceAssigned(rst.getString(16));
				arrayResult.add(purchaseProgramDTO);
			}			
			
		}catch (SQLException sqlException){
				PurchaseProgramException purchaseProgramException = null;
				sqlException.printStackTrace(System.out);
				purchaseProgramException= new PurchaseProgramException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
				throw purchaseProgramException;
			}finally {
				try{
					closeConnection(preparedStatement.getConnection());
				}catch(Exception exception){
				}
			}
		return arrayResult;	
	}
	/**
	 * Delete a Purchase Program By Code
	 * @author Edgar Joao
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public void deletePurchaseProgram(PurchaseProgramDTO entity) throws PurchaseProgramException {
		PreparedStatement preparedStatement =  null;		
		SystemParameterCollection systemParameterCollection = null;
		SystemParameterBean systemParameterBean = null;
		try {
			preparedStatement = prepareSQL(SP_DELETE_PURCHASE_ORDER, DATASOURCE);
			preparedStatement.setInt(1, entity.getPurchaseOrderId());
			executeInsert(preparedStatement);
		}catch(PoolException poolException){
			PurchaseProgramException purchaseProgramException = null;
			poolException.printStackTrace(System.out);
			purchaseProgramException = new PurchaseProgramException(poolException, CornPPException.LAYER_DAO, CornPPException.ACTION_DELETE);
			throw purchaseProgramException;		
		}catch (SQLException sqlException){
			PurchaseProgramException purchaseProgramException = null;
			systemParameterCollection = SystemParameterCollection.getInstance();
			systemParameterBean = systemParameterCollection.getSystemParameterBean(DBDAO.FOREIGN_KEY_ERROR_CODE);
			if(sqlException.getErrorCode() == systemParameterBean.getIntValue()){
				purchaseProgramException = new PurchaseProgramException(DBDAO.FOREIGN_KEY_ERROR_KEY, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}else{
				purchaseProgramException = new PurchaseProgramException(sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_DELETE);
			}
			sqlException.printStackTrace(System.out);
			throw purchaseProgramException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
	}
}